﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InterpriseSuiteEcommerceCommon;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;
using System.Data;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public class StoreLocatorDA
    {
        public static IEnumerable<SystemWareHouse> GetDealersFromCustomerAsWareHouse()
        {
            var warehouseFromCustomer = new List<SystemWareHouse>();

            using(var connection = DB.NewSqlConnection())
            {
                string sql = "SELECT DISTINCT C.CustomerName AS Description, c.Country, P.Counter, P.PostalCode, P.County, P.City, C.Address, C.State, P.Latitude, P.Longitude, P.IsActive " +
                             "FROM SystemPostalCode P INNER JOIN Customer C ON P.PostalCode = C.PostalCode AND P.CountryCode = C.Country AND P.City = C.City AND (IsNull(C.county, P.County) = P.County)" +
                             "WHERE C.IsActive = 1 AND C.CustomerTypeCode = 'Dealer' AND C.ShowOnStoreLocator = 1";

                connection.Open();
                var reader = DB.GetRS(sql, connection);

                while (reader.Read())
                {
                    warehouseFromCustomer.Add(new SystemWareHouse()
                                            {
                                                Coordinate = new Coordinate()
                                                {
                                                    Latitude = reader["Latitude"].ToString().TryParseDecimal().Value,
                                                    Longtitude = reader["Longitude"].ToString().TryParseDecimal().Value
                                                },
                                                Description = reader["Description"].ToString(),
                                                Address = new AddressDTO()
                                                {
                                                    address = reader["address"].ToString(),
                                                    state = reader["state"].ToString(),
                                                    postalCode = reader["postalcode"].ToString(),
                                                    country = reader["country"].ToString(),
                                                    city = reader["city"].ToString(),
                                                    id = reader["Counter"].ToString(),
                                                }
                                            });
                }

                connection.Close();

            }

            return warehouseFromCustomer;
        }

        public static IEnumerable<SystemWareHouse> GetWarehouses()
        {
            var systeWarehouses = new List<SystemWareHouse>();

            using (var zipCodeGateWay = new Interprise.Framework.SystemManager.DatasetGateway.PostalCodeDatasetGateway())
            {
                using (var zipCodeFacade = new Interprise.Facade.SystemManager.PostalCodeFacade(zipCodeGateWay))
                {
                    var strZipCodeCommandSet = new string[][] { new string[] { "SystemPostalCode", "eCommerceReadSystemWarehousePostalCode" } };

                    zipCodeFacade.LoadDataSet(strZipCodeCommandSet, Interprise.Framework.Base.Shared.Enum.ClearType.Specific, Interprise.Framework.Base.Shared.Enum.ConnectionStringType.Online);

                    //if we have some warehouses with zip codes, try and get the address to them
                    if (zipCodeGateWay.SystemPostalCode.Rows.Count > 0)
                    {
                        using (var warehouseGateway = new Interprise.Framework.Inventory.DatasetGateway.WarehouseDatasetGateway())
                        {
                            using (var warehouseFacade = new Interprise.Facade.Inventory.WarehouseFacade(warehouseGateway))
                            {
                                string[][] strWarehouseCommandSet = new string[][] { new string[] { "InventoryWarehouse", "ReadInventoryWarehouse" } };
                                warehouseFacade.LoadDataSet(strWarehouseCommandSet, Interprise.Framework.Base.Shared.Enum.ClearType.Specific, Interprise.Framework.Base.Shared.Enum.ConnectionStringType.Online);

                                //call the function to add the pins for each warehouse
                                foreach (DataRow row in zipCodeGateWay.SystemPostalCode.Rows)
                                {
                                    var rowWarehouse = warehouseGateway.InventoryWarehouse.Select(string.Format("PostalCode = '{0}' AND Country = '{1}' AND City = '{2}' AND County = '{3}'", row["PostalCode"].ToString(), row["CountryCode"].ToString(), row["City"].ToString(), row["County"].ToString()));
                                    if (rowWarehouse.Length >= 1) //found an exact match - send formatted address info
                                    {
                                        foreach (var rowWh in rowWarehouse)
                                        {
                                            systeWarehouses.Add(new SystemWareHouse()
                                            {
                                                Coordinate = new Coordinate()
                                                {
                                                    Latitude = row["Latitude"].ToString().TryParseDecimal().Value,
                                                    Longtitude = row["Longitude"].ToString().TryParseDecimal().Value
                                                },
                                                Description = rowWh["WarehouseDescription"].ToString(),
                                                Address = new AddressDTO()
                                                {
                                                    address = rowWh["address"].ToString(),
                                                    state = rowWh["state"].ToString(),
                                                    postalCode = rowWh["postalcode"].ToString(),
                                                    country = rowWh["country"].ToString(),
                                                    city = rowWh["city"].ToString(),
                                                    id = rowWh["Counter"].ToString(),
                                                }
                                            });
                                        }
                                    }
                                    else
                                    {
                                        systeWarehouses.Add(new SystemWareHouse()
                                        {
                                            Coordinate = new Coordinate()
                                            {
                                                Latitude = row["Latitude"].ToString().TryParseDecimal().Value,
                                                Longtitude = row["Longitude"].ToString().TryParseDecimal().Value
                                            },
                                            Description = row["City"].ToString()
                                        });
                                    }
                                }
                            }
                        }
                    }
                }
            }

            return systeWarehouses;

        }

        public static IEnumerable<SystemWareHouse> GetDealersAndWarehouses(StoreType storeType)
        {
            IEnumerable<SystemWareHouse> all = null;

            switch (storeType)
            { 
                case StoreType.All:

                    var warehouses = GetWarehouses();
                    var dealders = GetDealersFromCustomerAsWareHouse();
                    all = warehouses.Union(dealders);

                    break;
                case StoreType.Warehouse:

                    all = GetWarehouses();
                    break;

                case  StoreType.Dealer:
                    all = GetDealersFromCustomerAsWareHouse();
                    break;

                default:
                    break;
            }

            return all;
        }
    }
}
